package fieldmaps.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.postgresql.util.PGobject;

import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.LinearRing;
import com.vividsolutions.jts.geom.Polygon;
import com.vividsolutions.jts.geom.impl.CoordinateArraySequence;


public abstract class DatabaseGenericMap implements GenericMap {

	/**
	 * Informacion para la conexion a la base de datos.
	 */
	private int mapId = -1;
	protected ResultSet iterationSet;

	/**
	 * Logging.
	 */
	private static Logger LOGGER = Logger.getLogger(DatabaseGenericMap.class);
	private static Pattern coordenatesPattern = Pattern.compile("([-\\d\\.E]+)\\s+([-\\d\\.E]+)", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);


	protected int getMapId() {
		return mapId;
	}

	protected void setMapId(int mapId) {
		this.mapId = mapId;
	}
	
	public boolean isMapLoaded() {
		return (this.mapId != -1);
	}

	@Override
	public void addCell(Coordinate[] coordinates, int value) throws SQLException {
		try {
			StringBuilder statementString = new StringBuilder();
			statementString.append("INSERT INTO map_cells (map_id, cell_value, geometry) VALUES (");
			statementString.append(getMapId());
			statementString.append("," + value);
			statementString.append(", ST_GeomFromText(" + polygonToText(coordinates) + "));");
			Statement statement = DatabaseConnection.getConnectionInstance().createStatement();
			statement.execute(statementString.toString());
		} catch (NullPointerException e2) {
			LOGGER.error(e2.getMessage());
		}
	}
	
	List<Polygon> generatePolygonList(ResultSet result) throws SQLException {

		List<Polygon> aux = new LinkedList<Polygon>();

		while(result.next()) {
			List<Coordinate> coordinates = new ArrayList<Coordinate>();
			PGobject pgObject = (PGobject)result.getObject("geometry");
			Matcher m = coordenatesPattern.matcher(pgObject.getValue());
			while (m.find()) {
				Coordinate coordinate = new Coordinate();
				coordinate.x = Double.valueOf(m.group(1));
				coordinate.y = Double.valueOf(m.group(2));
				coordinates.add(coordinate);
			}
			LinearRing linearRing = new LinearRing(new CoordinateArraySequence(coordinates.toArray(new Coordinate[0])), new GeometryFactory());
			Polygon polygon = new Polygon(linearRing, null, new GeometryFactory());
			polygon.setUserData(iterationSet.getObject("cell_value"));
			aux.add(polygon);
		}

		return aux;
	}

	@Override
	public List<Polygon> getAllCells() throws SQLException {
		
		PreparedStatement statement = DatabaseConnection.getConnectionInstance().prepareStatement 
			("SELECT geometry, cell_value FROM map_cells WHERE map_Id = ?");
		statement.setInt(1, getMapId());
		ResultSet result = statement.executeQuery();
		return generatePolygonList(result);
	}

	@Override
	public List<Polygon> getCell(Coordinate coordinate) {
		List<Polygon> cells = new LinkedList<Polygon>();
		try {
			double x = coordinate.x;
			double y = coordinate.y;
			PreparedStatement statement = DatabaseConnection.getConnectionInstance().prepareStatement("SELECT geometry, cell_value " +
					"FROM map_cells WHERE map_id = " + getMapId() + " AND ST_CONTAINS(geometry, 'POINT(" + x + " " + y +")');");
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PGobject pgObject = (PGobject) rs.getObject("geometry");
				Polygon polygon = getCellFromObject(pgObject);
				polygon.setUserData(rs.getInt("cell_value"));
				cells.add(polygon);
			}
		} catch (SQLException e) {
			LOGGER.error("Cannot perform SQL query!");
		}
		return cells;
	}

	@Override
	public List<Polygon> getCellNeighbors(Polygon cell) {
		List<Polygon> neighbors = new LinkedList<Polygon>();
		try {
			StringBuilder sb = new StringBuilder("SELECT geometry, cell_value " +
					"FROM map_cells WHERE map_Id = " + getMapId() + " AND ST_DISTANCE(geometry, ST_GeomFromText(" + polygonToText(cell) + ")) < 10E-14;");
			PreparedStatement statement = DatabaseConnection.getConnectionInstance().prepareStatement(sb.toString());
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PGobject pgObject = (PGobject) rs.getObject("geometry");
				Polygon polygon = getCellFromObject(pgObject);
				polygon.setUserData(rs.getInt("cell_value"));
				if (!polygon.equals(cell)) {
					neighbors.add(polygon);
				}
			}
			return neighbors;
		} catch (SQLException e) {
			LOGGER.error("Cannot perform SQL query!");
		}
		return null;
	}

	@Override
	public void startCellIteration() {
		try {
			PreparedStatement statement = DatabaseConnection.getConnectionInstance().prepareStatement("SELECT geometry, cell_value " +
					"FROM map_cells WHERE map_Id = ?");
			statement.setInt(1, getMapId());
			iterationSet = statement.executeQuery();
		} catch (SQLException e) {
			LOGGER.error("Cannot perform SQL query!");
		}
	}

	@Override
	public void resetCellIteration() {
		iterationSet = null;
	}

	@Override
	public Polygon getNextCell() throws NoSuchElementException {
		try {
			if (iterationSet != null) {
				if (iterationSet.next()) {
					PGobject pgObject = (PGobject) iterationSet.getObject("geometry");
					Polygon polygon = getCellFromObject(pgObject);
					polygon.setUserData(iterationSet.getObject("cell_value"));
					return polygon;
				}
				throw new NoSuchElementException("There's no more elements!");
			} 
			throw new NoSuchElementException("startIteration must be called!");
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
		}
		return null;
	}
	
	public List<Polygon> getPolygonsIntersection (Polygon polygon) {
		List<Polygon> intersection = new LinkedList<Polygon>();
		try {
			StringBuilder sb = new StringBuilder("SELECT geometry, cell_value FROM map_cells WHERE map_id = " + getMapId() + " AND ST_INTERSECTS(geometry, ST_GEOMFROMTEXT(" + polygonToText(polygon) + "));");
			PreparedStatement statement = DatabaseConnection.getConnectionInstance().prepareStatement(sb.toString());
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				PGobject pgObject = (PGobject) rs.getObject("geometry");
				Polygon p = getCellFromObject(pgObject);
				p.setUserData(rs.getInt("cell_value"));
				intersection.add(p);
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
		}
		return intersection;
	}

	@Override
	public Double getMapArea() {
		try {
			String s = new String("SELECT SUM(ST_AREA(geometry)) AS sum FROM map_cells WHERE map_id = " + getMapId() + ";");
			PreparedStatement statement = DatabaseConnection.getConnectionInstance().prepareStatement(s);
			ResultSet rs = statement.executeQuery();
			if (rs.next()) {
				return rs.getDouble("sum");
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
		}
		return null;
	}
	
	@Override
	public void getPolygonsIntersecArea (Polygon polygon, List<Polygon> outputPolygonsIntersection, List<Double> outputPolygonsAreas) {
		try {
			StringBuilder sb = new StringBuilder("SELECT geometry, cell_value, ST_AREA(ST_INTERSECTION(geometry, ST_GeomFromText(");
			sb.append(polygonToText(polygon));
			sb.append("))) AS area FROM map_cells WHERE map_id = ");
			sb.append(getMapId());
			sb.append(" AND ST_INTERSECTS(geometry, ST_GeomFromText(");
			sb.append(polygonToText(polygon));
			sb.append("));");
			PreparedStatement statement = DatabaseConnection.getConnectionInstance().prepareStatement(sb.toString());
			ResultSet rs = statement.executeQuery();
			Double area;
			while (rs.next()) {
				area = rs.getDouble("area");
				if (area > 0.0) {
					PGobject pgObject = (PGobject) rs.getObject("geometry");
					Polygon p = getCellFromObject(pgObject);
					p.setUserData(rs.getInt("cell_value"));
					outputPolygonsIntersection.add(p);
					outputPolygonsAreas.add(area);
				}
			}
		} catch (SQLException e) {
			LOGGER.error(e.getMessage());
		}
	}

	protected void loadMapId(String name) throws SQLException {
		PreparedStatement statement = DatabaseConnection.getConnectionInstance().prepareStatement("SELECT map_id FROM map_table WHERE map_name = ?");
		statement.setString(1, name);
		ResultSet rs = statement.executeQuery();
		if (rs.next()) {
			setMapId(Integer.valueOf(rs.getString("map_id")));
		}
	}
	
	protected String polygonToText (Polygon polygon) {
		return polygonToText(polygon.getCoordinates());
	}
	
	protected String polygonToText (Coordinate[] coordinates) {
		StringBuilder sb = new StringBuilder();
		sb.append("'POLYGON((");
		for (int i = 0; i < coordinates.length; i++) {
			sb.append(coordinates[i].x + " " + coordinates[i].y);
			if (i != coordinates.length-1) {
				sb.append(",");
			}
		}
		sb.append("))'");
		return sb.toString();
	}

	private Polygon getCellFromObject(PGobject polygon) {
		List<Coordinate> coordinates = new ArrayList<Coordinate>();
		Matcher m = coordenatesPattern.matcher(polygon.getValue());
		while (m.find()) {
			Coordinate coordinate = new Coordinate();
			coordinate.x = Double.valueOf(m.group(1));
			coordinate.y = Double.valueOf(m.group(2));
			coordinates.add(coordinate);
		}
		LinearRing linearRing = new LinearRing(new CoordinateArraySequence(coordinates.toArray(new Coordinate[0])), new GeometryFactory());
		return new Polygon(linearRing, null, new GeometryFactory());
	}

	protected Object getInterpolatedValue(Polygon polygon, List<Polygon> polygonsIntersection, List<Double> areasPolygonsIntersection) {
		Double polygonArea = polygon.getArea();
		Integer polygonsIntersectionSize = polygonsIntersection.size();
		if (polygonsIntersectionSize == 1) {
			return (int)(polygonArea / polygonsIntersection.get(0).getArea()) * (Integer)polygonsIntersection.get(0).getUserData(); 
		}
		Double value = 0.0;
		Polygon p;
		for (int i = 0; i < polygonsIntersectionSize; i++) {
			p = polygonsIntersection.get(i);
			value += (areasPolygonsIntersection.get(i) / p.getArea()) * (Integer)p.getUserData();		
		}
		return (new Long(Math.round(value))).intValue();
	}
}
